﻿-- Function: trigger_s_before_lns()

-- DROP FUNCTION trigger_s_before_lns();

CREATE OR REPLACE FUNCTION trigger_s_before_lns()
  RETURNS trigger AS
$BODY$
DECLARE
    table_master    varchar(60)        := 'cdr';
    table_part      varchar(60)        := '';
    str_year		int	:= 0;
    str_month		int	:= 0;
    chek_table_from	   varchar(60)        := '';
    chek_table_to	   varchar(60)       := '';
--hours integer :=0;
--night_hours smallint	:=20;
--day_hours smallint	:=8;
BEGIN
  -- Даём имя партиции --------------------------------------------------
	str_year := date_part( 'year', NEW.calltime )::int;
	str_month:= date_part( 'month', NEW.calltime )::int;
    table_part := table_master 
                     || '_y' || date_part( 'year', NEW.calltime )::text
                    || '_m' || date_part( 'month', NEW.calltime )::text;
IF (str_month<10)
 THEN  
       chek_table_from := 
                   date_part( 'year', NEW.calltime )::text
                    || '-' ||'0'||date_part( 'month', NEW.calltime )::text||'-01';
 ELSE
      chek_table_from := 
                   date_part( 'year', NEW.calltime )::text
                    || '-' || date_part( 'month', NEW.calltime )::text||'-01';
 END IF ; 
IF (str_month=12)
  THEN   chek_table_to  := (str_year+1)::text|| '-01-01';  
ELSIF (str_month<9)
THEN  chek_table_to  := str_year::text || '-' || '0' ||(str_month+1)::text||'-01';
ELSE
 chek_table_to  := str_year::text || '-' || (str_month+1)::text||'-01';
END IF ; 
   -- Проверяем партицию на существование --------------------------------
    PERFORM 
        1 
    FROM 
        pg_class 
    WHERE 
        relname = table_part
    LIMIT	
        1;
   -- Если её ещё нет, то создаём --------------------------------------------
    IF NOT FOUND
    THEN
        -- Cоздаём партицию, наследуя мастер-таблицу --------------------------
       EXECUTE '
            CREATE TABLE ' || table_part || ' (
            CHECK ( calltime >= DATE '''||chek_table_from||''' AND calltime < DATE'''||chek_table_to||'''), 

            id bigint NOT NULL DEFAULT nextval(''' || table_master || '_id_seq''::regclass),
             CONSTRAINT ' || table_part || '_id_pk PRIMARY KEY (id) )
            INHERITS ( ' || table_master || ' )
            WITH ( OIDS=FALSE ) ';
       -- Создаём индексы для текущей партиции -------------------------------
        EXECUTE '
            CREATE INDEX ' || table_part || 'cdr_calltime_idx
            ON ' || table_part || '
            USING btree
            (calltime)
            ';
	 EXECUTE '
	CREATE INDEX ' || table_part || 'cdr_callstatus_idx
            ON ' || table_part || '
	USING btree
	(callstatus)
	';
	 EXECUTE '
	CREATE INDEX ' || table_part || 'cdr_numbertodial_idx
            ON ' || table_part || '
	USING btree
	(numbertodial)
	';
	 EXECUTE '
	CREATE INDEX ' || table_part || 'cdr_numberfromdial_idx
            ON ' || table_part || '
	USING btree
	(numberfromdial)
	';
	 EXECUTE '
	CREATE INDEX ' || table_part || 'cdr_selectiontrunk_idx
            ON ' || table_part || '
	USING btree
	(selectiontrunk)
	';
	 EXECUTE '	
	CREATE INDEX ' || table_part || 'cdr_createtrunk_idx
            ON ' || table_part || '
	USING btree
	(createtrunk)
	';
            
    END IF;
        
--NEW.duration = NEW.endtime::timestamp - NEW.answertime::timestamp;
--hours =EXTRACT(hour FROM NEW.calltime); 
--if (hours > night_hours AND hours < day_hours)
--then NEW.daytime = 0;
--else NEW.daytime = 1;
--end IF;  

---return NEW;
    -- Вставляем данные в партицию --------------------------------------------
    EXECUTE '
        INSERT INTO ' || table_part || ' 
        SELECT ( (' || quote_literal(NEW) || ')::' || TG_RELNAME || ' ).*';

    RETURN NULL;

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trigger_s_before_lns()
  OWNER TO atslog;
-- Trigger: cdr_partition_trigger on cdr

-- DROP TRIGGER cdr_partition_trigger ON cdr;

CREATE TRIGGER cdr_partition_trigger
  BEFORE INSERT
  ON cdr
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_s_before_lns();

-- Function: trigger_voip_before_lns()

-- DROP FUNCTION trigger_voip_before_lns();

CREATE OR REPLACE FUNCTION trigger_voip_before_lns()
  RETURNS trigger AS
$BODY$
DECLARE
    table_master    varchar(60)        := 'voip';
    table_part      varchar(60)        := '';
    str_year		int	:= 0;
    str_month		int	:= 0;
    chek_table_from	   varchar(60)        := '';
    chek_table_to	   varchar(60)       := '';

BEGIN
  -- Даём имя партиции --------------------------------------------------
	str_year := date_part( 'year', NEW.setuptime )::int;
	str_month:= date_part( 'month', NEW.setuptime )::int;
    table_part := table_master 
                     || '_y' || date_part( 'year', NEW.setuptime )::text
                    || '_m' || date_part( 'month', NEW.setuptime )::text;
IF (str_month<10)
 THEN  
       chek_table_from := 
                   date_part( 'year', NEW.setuptime )::text
                    || '-' ||'0'||date_part( 'month', NEW.setuptime )::text||'-01';
 ELSE
      chek_table_from := 
                   date_part( 'year', NEW.setuptime )::text
                    || '-' || date_part( 'month', NEW.setuptime )::text||'-01';
 END IF ; 
IF (str_month=12)
  THEN   chek_table_to  := (str_year+1)::text|| '-01-01';  
ELSIF (str_month<9)
THEN  chek_table_to  := str_year::text || '-' || '0' ||(str_month+1)::text||'-01';
ELSE
 chek_table_to  := str_year::text || '-' || (str_month+1)::text||'-01';
END IF ; 
   -- Проверяем партицию на существование --------------------------------
    PERFORM 
        1 
    FROM 
        pg_class 
    WHERE 
        relname = table_part
    LIMIT	
        1;
   -- Если её ещё нет, то создаём --------------------------------------------
    IF NOT FOUND
    THEN
        -- Cоздаём партицию, наследуя мастер-таблицу --------------------------
       EXECUTE '
            CREATE TABLE ' || table_part || ' (
            CHECK ( setuptime >= DATE '''||chek_table_from||''' AND setuptime < DATE'''||chek_table_to||'''), 

            id bigint NOT NULL DEFAULT nextval(''' || table_master || '_id_seq''::regclass),
             CONSTRAINT ' || table_part || '_id_pk PRIMARY KEY (id),
              CONSTRAINT ' || table_part || '_md5_uniq UNIQUE(md5) )
            INHERITS ( ' || table_master || ' )
            WITH ( OIDS=FALSE ) ';
       -- Создаём индексы для текущей партиции -------------------------------
        EXECUTE '
            CREATE INDEX ' || table_part || 'voip_setuptime_idx
            ON ' || table_part || '
            USING btree
            (setuptime)
            ';
    END IF;
        
    -- Вставляем данные в партицию --------------------------------------------
    EXECUTE '
        INSERT INTO ' || table_part || ' 
        SELECT ( (' || quote_literal(NEW) || ')::' || TG_RELNAME || ' ).*';

    RETURN NULL;

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trigger_voip_before_lns()
  OWNER TO atslog;
-- Trigger: voip_partition_trigger on voip

-- DROP TRIGGER voip_partition_trigger ON voip;

CREATE TRIGGER voip_partition_trigger
  BEFORE INSERT
  ON voip
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_voip_before_lns();
